package com.wjr.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wjr.dao.DeptDao;
import com.wjr.model.Dept;
import com.wjr.utils.DBUtil;


public class DeptDaoImpl implements DeptDao{

	Connection conn;
	PreparedStatement ptmt;
	ResultSet rs;
	
	@Override
	public boolean insert(Dept t) {
		conn = DBUtil.getconnection();
		boolean flag = true;      // ptmt.execute() 执行成功返回 false
		String sql = "insert into dept(dno,dname,ddesc) values(?,?,?)";
		try{
		ptmt = conn.prepareStatement(sql);
	    ptmt.setString(1, t.getDno());
	    ptmt.setString(2, t.getDname());
	    ptmt.setString(3, t.getDdesc());
	    
	    flag = ptmt.execute(); 
	    
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return flag;
	}

	@Override
	public boolean delete(int id) {
		conn = DBUtil.getconnection();
		String sql = "delete from dept where id=?";
		boolean flag = true;
		try{
		ptmt = conn.prepareStatement(sql);
	    ptmt.setInt(1, id);  
	    flag = ptmt.execute();
	    
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean update(Dept t) {
		conn = DBUtil.getconnection();
		String sql = "update dept set dno=?,dname=?,ddesc=? where id=?";
		System.out.println("-------"+t.getId());
		
		boolean flag = true;
		try{
		ptmt = conn.prepareStatement(sql);
	    ptmt.setString(1, t.getDno());
	    ptmt.setString(2, t.getDname());
	    ptmt.setString(3, t.getDdesc());
	    ptmt.setInt(4,t.getId());
	    flag = ptmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
		
	}

	@Override
	public List<Dept> findById(String no) {
		Dept dept = null;
		conn = DBUtil.getconnection();
		List<Dept>list = new ArrayList<Dept>();
		String sql = "" + " select * from dept " + " where dno=? ";
		try{
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, no);
		rs = ptmt.executeQuery();
	    while (rs.next())
	    {
	      dept = new Dept();
	      dept.setId(rs.getInt("id"));
	      dept.setDno(rs.getString("dno"));
	      dept.setDname(rs.getString("dname"));
	      dept.setDdesc(rs.getString("ddesc"));
	      
	      list.add(dept);
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<Dept> findAll() {
		List<Dept>list = new ArrayList<Dept>();
		Dept dept = null;
		conn = DBUtil.getconnection();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from dept");
		try{
		ptmt = conn.prepareStatement(sb.toString());
		rs = ptmt.executeQuery();
	    while (rs.next())
	    {
	      dept = new Dept();
	      dept.setId(rs.getInt("id"));
	      dept.setDno(rs.getString("dno"));
	      dept.setDname(rs.getString("dname"));
	      dept.setDdesc(rs.getString("ddesc"));
	      
	      list.add(dept);
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

}
